package com.cjc.eas.dao.teacherDao;

import com.cjc.eas.domain.Student;
import com.cjc.eas.domain.Teacher;
import com.cjc.eas.utils.ConnectDatabaseUtils;
import com.sun.rowset.CachedRowSetImpl;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * ClassName :TeacherDao <br/>
 * Date :2021/5/28 21:40 <br/>
 *
 * @author :GL <br/>
 * @since :jdk 1.8
 */
public class TeacherDao {
    static private Connection conection = null;
    static {
        conection = ConnectDatabaseUtils.getConection();
    }
    /**
      *@Description 教师查看个人信息，根据职工号查询
      *@params
      *@return
      *@author fades 未测试
      *@since
      */
    public static Teacher getTeacherByWno(String Wno){
        Teacher teacher = new Teacher();
        try{
            PreparedStatement statement = conection.prepareStatement("select * from t_Teacher where tchWno = ?");
            statement.setString(1,Wno);
            try{
                ResultSet resultSet = statement.executeQuery();
                resultSet.next();
                 teacher = transferStu(resultSet);

                return teacher;
            }catch (Exception ex){
                ex.printStackTrace();
            }
        }catch (SQLException ex){
            ex.printStackTrace();
        }finally {
            try {
                if (conection != null){
                    conection.close();
                }
                conection = null;
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        return teacher;
    }
    @Test
    public void test1(){
        Teacher teacher = getTeacherByWno("t1002");
        System.out.println(teacher.getTchName()+" "+teacher.getTchAcademic()+" "+teacher.getTchDepartment());
    }

    /**
      *@Description 教师查看班级总成绩,因为班级所有学生的选课是不一样的所以无法确定该方法的返回类型，故将该方法分解为两个方法
      *             这两个方法的返回值作为班级成绩表模式的表头和表体
      *@author fades 未测试
      *@since
      */

    /**
      *@Description 教师查看班级总成绩的方法一
      *@params
      *@return 返回类型为班级成绩表的表头，eg：
     *              姓名  Java编程基础 计算机组成原理 操作系统原理 数据结构 。。。。。。
      *@author fades
      *@since
      */
    public static String[] queryGScore_TableHead(String scoAnnual,String scoPeriod,String stuClassNum){
        int arraylen = 0;
        try{
            String sql = "select distinct couName from t_Course,t_Student,t_Score where t_Course.couCno=t_Score.scoCno and t_Score.scoSno=t_Student.stuSno";
            Statement stmt = conection.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            //用CachedRowSetImpl这个类的实例缓存结果集
//            CachedRowSetImpl rowSet = new CachedRowSetImpl();
//            rowSet.populate(rs);

            //获得返回数组的长度
            while(rs.next()){
                arraylen++;
            }
            //为返回的数组赋值
            int i =0;
            rs = stmt.executeQuery(sql);
            String[] result = new String[arraylen];
            while(rs.next()){
                String couName = rs.getString("couName");
                result[i]=couName;
                i++;
            }
            return  result;
        }catch ( SQLException e){
            e.printStackTrace();
        }
        return  null;
    }

    /**
      *@Description 教师查看班级总成绩的方法二
      *@params
      *@return 返回类型为班级成绩表的表体，eg：
     *           张三  99 87 54 空 84 65 空
     *           李四  84 75 空 74 96 55 41
     *           王五  空 74 56 74 空 54 71
      *@author fades
      *@since
      */
    public static Object[][] queryGScore_TableBody(String scoAnnual,String scoPeriod,String stuClassNum){
        //调用queryGScore_TableHead方法，获得表头的内容和表头的长度
        String[] CourseList = queryGScore_TableHead(scoAnnual,scoPeriod,stuClassNum);
        List<Student> StudnentList = new ArrayList<>(); //班级学生姓名和学号的数组
        try {
            String sql = "select stuSno,stuName from t_Student where stuClassNum=?";
            PreparedStatement ps = conection.prepareStatement(sql);
            ps.setString(1,stuClassNum);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                String stuSno = rs.getString("stuSno");
                String stuName = rs.getString("stuName");
                Student student = new Student();
                student.setStuSno(stuSno);
                student.setStuName(stuName);
                StudnentList.add(student);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }

        Object[][] courseTable= new Object[StudnentList.size()][CourseList.length];

        for(int i = 0;i<StudnentList.size();i++) {
            try {
                String sql = "select couName,scoScore from t_Score,t_Course where t_Score.scoCno=t_Course.couCno and scoSno=?";
                PreparedStatement ps = conection.prepareStatement(sql);
                ps.setString(1,StudnentList.get(i).getStuSno());
                ResultSet rs = ps.executeQuery();
                while (rs.next()){
                    String couName = rs.getString("couName");
                    int scoScore = rs.getInt("scoScore");
                    for(int j=0;j<CourseList.length;j++){
                        if(couName.equals(CourseList[j])){
                            courseTable[i][j]=scoScore;
                        }
                    }
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  courseTable;
    }
/**
  *@Description 测试通过 得到两个二维数组
  *@params
  *@return
  *@author fades
  *@since
  */
    @Test
    public  void test2(){
        String[] tableHead = queryGScore_TableHead("2020-2021","第二学期","B1901");
        Object[][] tableBody = queryGScore_TableBody("2020-2021","第二学期","B1901");
        for(String e : tableHead){
            System.out.print(e);
            System.out.print(" ");
        }
        System.out.println();
        for(int i=0;i<tableBody.length;i++){
            for(int j=0;j<tableBody[0].length;j++){
                System.out.print(tableBody[i][j]);
                System.out.print(" ");
            }
            System.out.println();
        }
    }

    /**
      *@Description 暂时为教师查看个人信息这一功能服务
      *@params
      *@return
      *@author fades
      *@since
      */
    public static Teacher  transferStu(ResultSet resultSet) {
        Teacher teacher = new Teacher();
        try{
            teacher.setTchWno(resultSet.getString(1));
            teacher.setTchName(resultSet.getString(2));
            teacher.setTchGender(resultSet.getString(3));
            teacher.setTchPosition(resultSet.getString(4));
            teacher.setTchNation(resultSet.getString(5));
            teacher.setTchProfessionalTitle(resultSet.getString(6));
            teacher.setTchAcademic(resultSet.getString(7));
            teacher.setTchDepartment(resultSet.getString(8));
            teacher.setTchContactNum(resultSet.getString(9));
            teacher.setTchWorkTime(resultSet.getString(10));
            teacher.setTchIntroduction(resultSet.getString(11));
        }catch (SQLException e){
            e.printStackTrace();
        }

        return teacher;
    }
}
